package org.hzy.test1;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hzy.entity.Dept;
import org.hzy.entity.Emp;

public class HQLtest {

	/**
	 * @param args
	 */
	static Configuration con=new Configuration().configure();
	static SessionFactory fac=con.buildSessionFactory();
	static Session sess=fac.getCurrentSession();
	
	//返回对象
	static void ts1(String hql){
		Transaction tran=sess.beginTransaction();
		Query query=sess.createQuery(hql);
		List<Dept> list=query.list();
		System.out.println(list.size());
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i).getDeptno());
		}
//		
//		Iterator it=list.iterator();
//		while(it.hasNext()){
//			Object[] obj=(Object[]) it.next();
////			Dept d=(Dept) obj[0];
//			System.out.println(obj[0]+"   "+obj[1]+"   "+obj[2]);
//		}
		tran.commit();
	}
	
	//查询属性
	static void ts2(String hql){
		Transaction tran=sess.beginTransaction();
		Query query=sess.createQuery(hql);
		List list=query.list();
		Iterator it=list.iterator();
		while(it.hasNext()){
//			System.out.println(it.next().getClass());
			Object[] obj=(Object[])it.next();//一个字段时直接next()
			System.out.println(obj[0]+"  "+obj[1]);
//			System.out.println(it.next());
		}
		tran.commit();
	}
	
	//参数
	static void ts3(Object obj){
		Transaction tran=sess.beginTransaction();
		List names=new ArrayList();
		names.add(0, "CLARK");
		names.add(1, "KING");
		Query query=sess.createQuery("select e.empno,e.ename from Emp e where e.ename='"+obj+"'");
		
//		Query query=sess.createQuery("select e.empno,e.ename from Emp e where e=?");//like %错误
//		query.setEntity(0, (Emp)obj);
		
/*		Query query=sess.createQuery("select e.empno,e.ename from Emp e where e.ename in(:namelist)");
		query.setParameterList("namelist", names);*/
		
		
//		Query query=sess.createQuery("select e.empno,e.ename from Emp e where e.empno>:empno");
//		query.setInteger("empno",Integer.valueOf(obj.toString()));
		List list=query.list();
		Iterator it=list.iterator();
		while(it.hasNext()){
			Object[] object=(Object[]) it.next();
			System.out.println(object[0]+" "+object[1]);
		}
		tran.commit();
	}
	
	static void ts4(){
		Transaction tran=sess.beginTransaction();
//		Iterator iter=sess.createQuery("from Dept").iterate();//会发送多条查询语句
//		while(iter.hasNext()){
//			System.out.println(iter.next());
//		}
		
		List lis=sess.createQuery("from Dept").list();
		for (int i = 0; i < lis.size(); i++) {
			System.out.println(lis.get(i));
		}
		tran.commit();
	}
	static void ts5(){
		Transaction tran=sess.beginTransaction();
		Iterator it=sess.createQuery("select d.deptno,d.dname,d.loc from Dept d").list().iterator();
		
		while(it.hasNext()){
			Object[] obj=(Object[]) it.next();
			System.out.println(obj[0]+" "+obj[1]);
//			Dept dept=(Dept)obj[0];
//			System.out.println(dept);		
		}	
	}
	
	public static void main(String[] args) {
		// TODO Auto-generated method stub
/*		Transaction tran=sess.beginTransaction();
		List list=(List) sedss.createQuery("from Dept as dept where dept.deptno<?").setInteger(0, 11).list();
		System.out.println(list.size());
		tran.commit();*/
		
		
//		ts1("from Emp e where (1=2)");
//		ts1("from Dept d where d.dname like 'S%'");
		
//		ts2("select d.deptno,d.dname from Dept d order by d.deptno desc");
		
//		ts2("select max(d.deptno),min(d.deptno) from Dept d");
		
//		ts2("select count(e.empno),e.ename from Emp e group by e.ename");
		
//		ts1("from Dept d inner join d.emps e");
		
//		ts1("from Dept d left join d.emps"); //没加fetch查出来的是objec[]  加了则是对象
		
//		ts1("from Emp e inner join fetch e.dept");
		
//		ts1("from Emp e left join fetch e.dept");
		
//		ts1("from Dept d right join d.emps e");

//		ts1("select avg(d.deptno),sum(d.deptno),max(d.deptno),count(*) from Dept d");
		
//		ts1("select distinct e.ename,e.empno from Emp e");
		
//		ts1("from java.lang.Object o");//会查出所有的实体
		
//		ts1("from Dept d join d.emps e");//查询只有一个时候obj数组不能转换  和一个字段一样  两个则不会
		
//		ts1("from Dept d where d.deptno not between 20 and 40");
		
//		ts1("from Dept d where d.emps.size>0");
		
//		ts1("from Dept d where size(d.emps)>0 and d.loc is not null");
		
//		ts1("from Dept d where maxelement(d.emps)>7369");
		
//		ts1("from Dept d where d.emps in elements(from emps e where e.empno=7369)");
		
//		ts1("select e.empno,sum(e),count(*) from Emp e group by e.empno");
		
//		ts1("from Dept d where d.deptno =some(select e.dept.deptno from Emp e)");
		
//		ts1("from Dept d where d.deptno in (select e.dept.deptno from Emp e)");
		
		
//		ts1("from Dept d where d.deptno >(select e.dept.deptno from Emp e where e.empno=7369)");
		
		
//		ts2("select e.empno from Emp e where e.empno>7566");
		
/*		Emp emp=new Emp();
		emp.setEmpno(7900);
//		emp.setEname("JONES");//只会根据ID
		emp=(Emp) sess.createQuery("from Emp as e where e=?").setEntity(0, emp).uniqueResult();	//like %错误
		System.out.println(emp.getJob());*/
		
/*		Transaction tran=sess.beginTransaction();
		Query que=sess.createQuery("from Emp e");
		
		que.setFirstResult(1);
		que.setMaxResults(2);
		List<Emp> list=que.list();
		
		System.out.println(list.size());
		
		for (Emp emp : list) {
			System.out.println(emp.getEmpno()+"  "+emp.getEname());
		}*/
		
		
//		ts3(7769);
		
//		ts3(emp);

//		ts4();
		
		
		
//		ts3("bb");
		
		
		
		Transaction tran=sess.beginTransaction();
/*		Query query=sess.createQuery("from Emp e where to_char(e.hiredate,'yyyy-mm')=?");
		query.setParameter(0, "1980-12");
		List list=query.list();
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
		}*/
		sess.enableFilter("myFilter").setParameter("myFilterParam", 10);
		Query qu=sess.createQuery("from Dept d");
		System.out.println(qu.list().size());
		
		tran.commit();
		
	}

}
